<?php
include_once 'mysql_connect.php';
class MyDict {
	public static function addWord($key, $meaning, $note) {
		$word_table_name = WORD_TABLE_NAME;
		$key = mysql_escape_string($key);
		$meaning = mysql_escape_string($meaning);
		$note = mysql_escape_string($note);
		$sql = "INSERT INTO `{$word_table_name}`(`key`,`meaning`,`note`,`created`,`updated`) VALUES('{$key}','{$meaning}','{$note}',NOW(),NOW());";
		mysql_query($sql);
	}
	public static function editWord($key, $meaning, $note) {
		$word_table_name = WORD_TABLE_NAME;
		$key = mysql_escape_string($key);
		$meaning = mysql_escape_string($meaning);
		$note = mysql_escape_string($note);
		$sql = "UPDATE `{$word_table_name}` SET `meaning`='{$meaning}',`note`='{$note}',`updated`=NOW() WHERE `key`='{$key}';";
		mysql_query($sql);
	}
	public static function queryKey($key) {
		$word_table_name = WORD_TABLE_NAME;
		$key = mysql_escape_string($key);
		$query = "SELECT * FROM `{$word_table_name}` WHERE `key`='{$key}'";
		$row = db_query_row($query);
		return $row;
	}
	public static function addMeaningCat($name, $parent_id) {
		$word_cat_table_name = WORD_CAT_TABLE_NAME;
		$name = mysql_escape_string($name);
		$sql = "INSERT INTO `{$word_cat_table_name}`(`name`,`parent_id`,`type`) VALUES('{$name}',{$parent_id},'meaning');";
		mysql_query($sql);
	}
	public static function updateMeaningCats($key, $cats) {
		$word = self::queryKey($key);
		if (!$word) {
			return false;
		}
		$word_id = $word['id'];
		
		global $con;
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$query = "SELECT * FROM `mydict_word_meta`
			WHERE `word_id`={$word_id} AND `meta_key`='meaning';";
		$result = db_query($query);
		if (mysql_num_rows($result)) {
			$sql = "UPDATE `{$word_meta_table_name}`
				SET `meta_value`='{$cats}'
				WHERE `meta_key`='meaning' AND `word_id`={$word_id};";
			$result = db_query($sql);
			$effected = mysql_affected_rows();
		} else {
			$sql = "INSERT INTO `{$word_meta_table_name}`(`word_id`,`meta_key`,`meta_value`)
				VALUES({$word_id},'meaning','{$cats}');";
			db_query($sql);
		}
	}
	
	/**
	 * Get Meaning Cat as Tree
	 * @param string $key
	 * @return Tree: Node as Assoc array {id => {data, children}}
	 */
	public static function getMeaningCatTree($key = null) {
		global $con;
		$word_table_name = WORD_TABLE_NAME;
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$word_cat_table_name = WORD_CAT_TABLE_NAME;
		$select = 0;
		if (isset($key)) {
			$query = "SELECT *
				FROM `{$word_table_name}` word JOIN `{$word_meta_table_name}` word_meta
				ON word.`id`=word_meta.`word_id` AND `key`='{$key}' AND `meta_key`='meaning';";
			$row = db_query_row($query);
			if ($row) {
				$meaning_cat = $row['meta_value'];
				$select = "IF(`id` IN ({$meaning_cat}),1,0)";
			}
		}
		$query = "SELECT *,{$select} as `select`,0 as `expand` FROM `{$word_cat_table_name}` WHERE `type`='meaning'";
		$result = db_query($query);
		$nodes_list = array();
		$tree = array();
		while ($row = mysql_fetch_array($result)) {
			if (!isset($row['parent_id']) || empty($row['parent_id'])) {
				$tree[$row['id']] = array('data' => $row, 'children' => array());
			} else {
				$nodes_list[$row['parent_id']][] = $row;
			}
		}
		foreach ($tree as $root) {
			self::_putNodeRecur($tree[$root['data']['id']], $nodes_list);
		}
		return $tree;
	}
	public static function getMeaningCats($key) {
		$meaning_meta = self::getMeta($key, 'meaning');
		$meaning_cats = array();
		if (empty($meaning_meta)) {
			return $meaning_cats;
		}
		$meaning_meta = $meaning_meta[0];
		$word_cat_table_name = WORD_CAT_TABLE_NAME;
		$query = "SELECT * FROM `{$word_cat_table_name}`
			WHERE `id` IN ($meaning_meta);";
		$result = db_query($query);
		while ($row = mysql_fetch_assoc($result)) {
			$meaning_cats[] = $row;
		}
		return $meaning_cats;
	}
	
	/**
	 * Put recursively node list into a tree node
	 * @param Node $node<p>
	 * Tree node that the child nodes put to
	 * Node as Assoc array {id => {data, children}}</p>
	 * @param Array $nodes_list<p>
	 * Node list as Assoc array {parent_id => {children}}</p>
	 */
	private static function _putNodeRecur(&$node, &$nodes_list) {
		if (!isset($nodes_list[$node['data']['id']])) {
			return;
		}
		$children_nodes = $nodes_list[$node['data']['id']];
		unset($nodes_list[$node['data']['id']]);
		foreach ($children_nodes as $row) {
			$node['children'][$row['id']] = array('data' => $row, 'children' => array());			
			self::_putNodeRecur($node['children'][$row['id']], $nodes_list);
			$child_data = $node['children'][$row['id']]['data'];
			if (($child_data['select'] == 1) || ($child_data['expand'] == 1)) $node['data']['expand'] = 1;
		}
	}
	
	private static function _expandToSelectedRecur(&$node) {
		
	}
	public static function checkCorrect($word_id) {
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$sql = "UPDATE `{$word_meta_table_name}` SET `meta_value`=(`meta_value`+1) WHERE `word_id`={$word_id} AND `meta_key`='check_correct';";
		db_query($sql);
		if (mysql_affected_rows() == 0) {
			$sql = "INSERT INTO `{$word_meta_table_name}`(`word_id`,`meta_key`,`meta_value`) VALUES ({$word_id},'check_correct','1');";
			db_query($sql);
		}
	}
	public static function checkIncorrect($word_id) {
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$sql = "UPDATE `{$word_meta_table_name}` SET `meta_value`=(`meta_value`+1) WHERE `word_id`={$word_id} AND `meta_key`='check_incorrect';";
		db_query($sql);
		if (mysql_affected_rows() == 0) {
			$sql = "INSERT INTO `{$word_meta_table_name}`(`word_id`,`meta_key`,`meta_value`) VALUES ({$word_id},'check_incorrect','1');";
			db_query($sql);
		}
	}
	public static function getReviewWords($date_query = 'all', $mode = 'rotating', $limit = 100) {
		$word_table_name = WORD_TABLE_NAME;
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$date_cond = '';
		if ($date_query != 'all') {
			if ($date_query == 'today') {
				$date_cond = " AND (DATE(word.`updated`) = DATE(NOW()))";
			} else {
				$date_cond = " AND YEAR(word.`updated`) = YEAR(NOW())";
				if ($date_query != 'this_year') {
					if ($date_query == 'this_week') {
						$date_cond .= " AND (WEEK(word.`updated`) = WEEK(NOW()))";
					} else {
						$date_cond .= " AND (MONTH(word.`updated`) = MONTH(NOW()))";
					}
				}
			}
		}
		$conds = 'WHERE (1=1)';
		$conds .= $date_cond;
		$having = '';
		$order = '`review` ASC';
		if ($mode == 'hard_word') {
			$order = '`mark` ASC';
			$having = 'HAVING (`review` > 0) AND (`mark` < 1)';
		}
		$query = "SELECT word.*,
		IFNULL(SUM(IF(`meta_key`='check_incorrect',0,word_meta.`meta_value`)),0) AS `check_correct`,
		IFNULL(SUM(IF(`meta_key`='check_correct',0,word_meta.`meta_value`)),0) AS `check_incorrect`,
		IFNULL(SUM(word_meta.`meta_value`),0) AS `review`,
		(IFNULL(SUM(IF(`meta_key`='check_incorrect',0,word_meta.`meta_value`)),0)/IFNULL(SUM(word_meta.`meta_value`),1)) AS `mark`
		FROM `{$word_table_name}` word LEFT JOIN `{$word_meta_table_name}` word_meta
		ON word.`id`=word_meta.`word_id` AND (word_meta.`meta_key`='check_correct' OR word_meta.`meta_key`='check_incorrect')
		{$date_cond}
		GROUP BY word.`id`
		{$having}
		ORDER BY {$order}, RAND()
		LIMIT 0,{$limit};";
		$result = db_query($query);
		$words = array();
		while ($row = mysql_fetch_array($result)) {
			$words[] = $row;
		}
		return $words;
	}
	
	/**
	 * Get word meta value
	 * @param string $key
	 * @param string $meta_key
	 * @return mixed
	 */
	public static function getMeta($key, $meta_key) {
		$word_table_name = WORD_TABLE_NAME;
		$word_meta_table_name = WORD_META_TABLE_NAME;
		$query = "SELECT `meta_value` 
				FROM `{$word_table_name}` word JOIN `{$word_meta_table_name}` word_meta
				ON word.`id`=word_meta.`word_id` AND `key`='{$key}' AND `meta_key`='{$meta_key}';";
		$meta_values = array();
		$result = db_query($query);
		while ($row = mysql_fetch_array($result)) {
			$meta_values[] = $row[0];
		}
		return $meta_values;
	}
}

function db_query($query) {
	global $con;
	$result = mysql_query($query, $con);
	if (!$result) {
		die('Could not run query ' . mysql_error());
	}
	return $result;
}
function db_query_row($query) {
	global $con;
	$result = db_query($query);
	$row = mysql_fetch_array($result);
	return $row;
}